/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;
import model.LogFile;
import pojo.Provider;

/**
 *
 * @author Mostafa
 */
//this class for all operations that can be done on  provider in database...
public class ProviderDao {

    private Connection c;

    public ProviderDao(Connection c) {
        this.c = c;
    }

    //this method for inserting new provider in database
    public boolean insert(Provider p) {
        boolean check = false;
        try {
            PreparedStatement ps = c.prepareStatement("insert into providers (name , type , headers ,table_name ,script_path) values ( ? ,? , ? , ? ,?)");
            ps.setString(1, p.getName());
            ps.setString(2, p.getType());
            ps.setString(3, p.getHeaders());
            ps.setString(4, p.getTableName());
            ps.setString(5, p.getScriptPath());
            int i = ps.executeUpdate();
            if (i == 1) {
                check = true;
                LogFile.log(" System ", " successfully added new provider with name [" + p.getName() + "] to the provider table ");
            } else {
                check = false;
                LogFile.log(" System ", " failed to add new provider with name [" + p.getName() + "] to the provider table ");
            }
        } catch (SQLException ex) {
            Logger.getLogger(ProviderDao.class.getName()).log(Level.SEVERE, null, ex);
            LogFile.log(" System ", " encountered the following problem while trying to add new provider to the provider table : [" + ex + "]");
        }
        return check;
    }

    public Provider getById(Integer id) {
        try {
            Provider p = new Provider();
            PreparedStatement ps = c.prepareStatement("select * from providers where p_id=?");
            ps.setInt(1, id);
            ResultSet rset = ps.executeQuery();
            if (rset.next()) {
                p.setPId(rset.getInt(1));
                p.setName(rset.getString(2));
                p.setType(rset.getString(3));
                p.setHeaders(rset.getString(4));
                p.setTableName(rset.getString(5));
                p.setScriptPath(rset.getString(6));
                LogFile.log(" System "," Successffully retreived All Available Data of Provider ["+p.getName()+"]");
                return p;
            } else {
                LogFile.log(" System "," didn't find any Available Data for Provider with ID : ["+id+"]");
                return null;
            }
        } catch (SQLException ex) {
            Logger.getLogger(ProviderDao.class.getName()).log(Level.SEVERE, null, ex);
            LogFile.log(" System "," enountered the Following Exception While trying to retreive Available Data for Provider with ID : ["+id+"] , the Error was : ["+ex+"]");
        }
        return null;
    }

    //get provider data by his name
    public Provider getByName(String name) {
        try {
            Provider p = null;
            PreparedStatement ps = c.prepareStatement("select * from providers where name=?");
            ps.setString(1, name);

            ResultSet rset = ps.executeQuery();
            if (rset.next()) {
                p = new Provider();
                p.setPId(rset.getInt(1));
                p.setName(rset.getString(2));
                p.setType(rset.getString(3));
                p.setHeaders(rset.getString(4));
                p.setTableName(rset.getString(5));
                p.setScriptPath(rset.getString(6));
                LogFile.log(" System ", " successfully retreived data of provider [" + p.getName() + "] from the provider table ");
                return p;
            } else {
                LogFile.log(" System ", " failed to retreive data of provider with name [" + name + "] , may be there is no providers with that name.");
                return null;
            }
        } catch (SQLException ex) {
            Logger.getLogger(ProviderDao.class.getName()).log(Level.SEVERE, null, ex);
            LogFile.log(" System ", " encountered the following problem while trying to retreive data of provider [" + name + "],the problem is :[" + ex + "]");
            return null;
        }
    }

    public Vector<Provider> getByPattern(String pattern) {
        try {
            Vector<Provider> providers = new Vector<Provider>();
            PreparedStatement ps = c.prepareStatement("select * from providers where name like ? ");
            ps.setString(1, pattern + "%");

            ResultSet rset = ps.executeQuery();

            while (rset.next()) {
                Provider p = new Provider();
                p.setPId(rset.getInt(1));
                p.setName(rset.getString(2));
                p.setType(rset.getString(3));
                p.setHeaders(rset.getString(4));
                p.setTableName(rset.getString(5));
                p.setScriptPath(rset.getString(6));
                providers.add(p);
                System.out.println("p name ====== " + p.getName() + "/////////");

            }
            return providers;
        } catch (SQLException ex) {
            Logger.getLogger(ProviderDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public Vector<String> getAllProviders() {
        try {
            Vector<String> providers = new Vector<String>();
            PreparedStatement ps = c.prepareStatement("select * from providers");
            ResultSet rset = ps.executeQuery();

            while (rset.next()) {
                providers.add(rset.getString(2));
                System.out.println("p name ====== " + rset.getString(2) + "/////////");

            }
            return providers;
        } catch (SQLException ex) {
            Logger.getLogger(ProviderDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public Vector<Provider> getAll() {
        try {
            Vector<Provider> providers = new Vector<Provider>();
            PreparedStatement ps = c.prepareStatement("select * from providers");
            ResultSet rset = ps.executeQuery();

            while (rset.next()) {
                Provider pro = new Provider();
                pro.setName(rset.getString(2));
                providers.add(pro);
                System.out.println("p name ====== " + rset.getString(2) + "/////////");
            }
            return providers;
        } catch (SQLException ex) {
            Logger.getLogger(ProviderDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    //method for updating provider profile data
    public boolean update(Provider p) {
        boolean check = false;
        try {
            boolean first = false;
            String updatetxt = "";
            if (p.getName() != null) {
                first = true;
                updatetxt = "name='" + p.getName() + "'";
            }
            if (p.getType() != null) {
                if (first) {
                    updatetxt += ", ";
                }
                first = true;
                updatetxt += "type='" + p.getType() + "'";
            }
            if (p.getHeaders() != null) {
                if (first) {
                    updatetxt += ", ";
                }
                first = true;
                updatetxt += "headers='" + p.getHeaders() + "'";
            }
            if (p.getTableName() != null) {
                if (first) {
                    updatetxt += ", ";
                }
                first = true;
                updatetxt += "table_name='" + p.getTableName() + "'";
            }
            if (p.getScriptPath() != null) {
                if (first) {
                    updatetxt += ", ";
                }
                first = true;
                updatetxt += "script_path='" + p.getScriptPath() + "'";
            }

            PreparedStatement ps = c.prepareStatement("update providers set " + updatetxt + " where p_id=?");
            ps.setInt(1, p.getPId());
            int i = ps.executeUpdate();
            if (i == 1) {
                check = true;
            }
        } catch (SQLException ex) {
            check = false;
            LogFile.log(" System ", " encountered the following exception while trying to update profile of " + p.getName() + " and this is the exception " + ex.getMessage());
            Logger.getLogger(ProviderDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return check;
    }

    //this method to delete provider by his name
    public boolean deleteByName(String name) {
        boolean check = false;
        try {
            PreparedStatement ps = c.prepareStatement("delete from providers where name=?");
            ps.setString(1, name);
            int i = ps.executeUpdate();
            if (i == 1) {
                check = true;
            } else {
                check = false;
            }
        } catch (SQLException ex) {
            check = false;
            LogFile.log(" System ", " encountered the following Exception while trying to delete provider " + name + " and this is the error " + ex.getMessage());
            Logger.getLogger(ProviderDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return check;
    }

    public boolean deleteById(Integer id) {
        boolean check = true;
        try {
            PreparedStatement ps = c.prepareStatement("delete from providers where p_id=?");
            ps.setInt(1, id);
            ps.execute();
        } catch (SQLException ex) {
            check = false;
            Logger.getLogger(ProviderDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return check;
    }

    //Drop Provider Table
    public boolean dropProviderTable(String tableName) {
        boolean check = false;
        try {
            PreparedStatement ps = c.prepareStatement("drop table " + tableName);
            int i = ps.executeUpdate();
            if (i == 1) {
                check = true;
            } else {
                check = false;
            }
        } catch (SQLException ex) {
            check = false;
            LogFile.log(" System ", " encountered the following exception  while trying to drop the table " + tableName + " and this is the error " + ex.getMessage());
            Logger.getLogger(ProviderDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return check;
    }
}
